package br.ufrj.neg.usuario.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.usuario.bean.UsuarioBean;
import br.ufrj.neg.util.Criptografer;

public class UsuarioModel {
	
	
	public UsuarioBean logar(String login, String senha){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT u.*,p.pape_ds_papel FROM USUARIO u "+
						 "LEFT JOIN papel p ON p.pape_cd_id = u.pape_cd_id " +
						 "WHERE USUA_DS_LOGIN = ? and USUA_CD_SENHA = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, login);
			ps.setString(2, Criptografer.md5(senha));
			rs = ps.executeQuery();
			if (rs.next()){
				UsuarioBean ub = new UsuarioBean();
				ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
				ub.setId(rs.getInt("USUA_CD_ID"));
				ub.setLogin(login);
				ub.setPapel(rs.getInt("PAPE_CD_ID"));
				ub.setPapelStr(rs.getString("pape_ds_papel"));
				ub.setSenha("");
				return ub;
			}else
				return null;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public int cadastraUsuario(UsuarioBean ub){
		
		if ( this.getUsuarioPorLogin(ub.getLogin()) == null ) {		
		
			Connection conn = ServiceLocator.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try{
				String sql;
				int id_usuario = -1;
				int id_qusuario = -1;
				
				sql = "INSERT INTO USUARIO(USUA_DS_USUARIO,USUA_DS_LOGIN,USUA_CD_SENHA,PAPE_CD_ID,USUA_CD_QUSU_ID) VALUES(?,?,?,?,?)";
				ps=conn.prepareStatement(sql);
				ps.setString(1,ub.getDescricao());
				ps.setString(2,ub.getLogin());
				ps.setString(3, Criptografer.md5(ub.getSenha()));
				ps.setInt(4, ub.getPapel());
				
				if( id_qusuario==-1){
					ps.setNull(5,java.sql.Types.INTEGER );	
				}
				else{
					ps.setInt(5, id_qusuario);
				}
				
				ps.executeUpdate();
				
				sql = "SELECT max(usua_cd_id) as \"num\" FROM usuario";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();				
				
				
				while (rs.next()) {
					id_usuario= rs.getInt("num");
				}	
				

				conn.commit();
				
				
				return id_usuario;
				
			}catch(Exception e){
				System.out.println(e);
				return -1;
			}finally{
				try{rs.close();}catch(Exception e){System.out.println(e);}
				try{ps.close();}catch(Exception e){System.out.println(e);}
				try{conn.close();}catch(Exception e){System.out.println(e);}
			}
			
		} else {
			return -2;
		}
	}
	
	public void alteraUsuario(UsuarioBean ub){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		try{
			String sql = "UPDATE USUARIO SET USUA_DS_USUARIO = ?," +
					"USUA_DS_LOGIN=?," +
					"USUA_CD_SENHA=?," +
					"PAPE_CD_ID=? WHERE USUA_CD_ID=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,ub.getDescricao());
			ps.setString(2,ub.getLogin());
			ps.setString(3, Criptografer.md5(ub.getSenha()));
			ps.setInt(4, ub.getPapel());
			ps.setInt(5,ub.getId().intValue());
			ps.executeUpdate();
			conn.commit();			
			
		}catch(Exception e){
			System.out.println(e);
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public boolean removeUsuario(String idUsuario){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		try{
			String sql = "DELETE FROM USUARIO WHERE USUA_CD_ID=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,(Integer.parseInt(idUsuario)));
			ps.executeUpdate();
					
			conn.commit();
			return true;
		}catch(Exception e){			
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public boolean isNewLogin(String login){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT * FROM USUARIO WHERE upper(USUA_DS_LOGIN)=upper(?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, login);
			rs = ps.executeQuery();
			if (rs.next()){
				return false;
			}else{
				return true;
			}
		}catch(Exception e){
			System.out.println(e);
			return false;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
		
	public Collection<UsuarioBean> listaUsuario(){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Collection<UsuarioBean> c = new ArrayList<UsuarioBean>();
		try{
			String sql = "SELECT u.*,p.pape_ds_papel FROM USUARIO u "+
			 "LEFT JOIN papel p ON p.pape_cd_id = u.pape_cd_id " +
			 "ORDER BY USUA_DS_USUARIO";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while (rs.next()){
				UsuarioBean ub = new UsuarioBean();
				ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
				ub.setId(rs.getInt("USUA_CD_ID"));
				ub.setLogin(rs.getString("USUA_DS_LOGIN"));
				ub.setPapel(rs.getInt("PAPE_CD_ID"));
				ub.setPapelStr(rs.getString("pape_ds_papel"));
				ub.setSenha("");
				c.add(ub);
			}
			return c;
		}catch(Exception e){
			System.out.println(e);
			return c;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public UsuarioBean getUsuario(String id){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT u.*,p.pape_ds_papel FROM USUARIO u "+
			 "LEFT JOIN papel p ON p.pape_cd_id = u.pape_cd_id " +
			 "WHERE USUA_CD_ID=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.parseInt(id));
			rs = ps.executeQuery();
			if (rs.next()){
				UsuarioBean ub = new UsuarioBean();
				
				ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
				ub.setId(rs.getInt("USUA_CD_ID"));
				ub.setLogin(rs.getString("USUA_DS_LOGIN"));
				ub.setPapel(rs.getInt("PAPE_CD_ID"));
				ub.setPapelStr(rs.getString("pape_ds_papel"));
				ub.setSenha("");
				
				return ub;
			}
			return null;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}

	public int verificaNumeroAdministradores() {
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT count(*) as \"num\" FROM USUARIO WHERE pape_CD_ID=1";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()){
				return rs.getInt("num");
			}
			return 0;
		}catch(Exception e){
			System.out.println(e);
			return -1;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	public Collection<UsuarioBean> listaOutros(int id){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT * FROM USUARIO WHERE USUA_CD_ID<>?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			Collection<UsuarioBean> c = new ArrayList<UsuarioBean>();
			while (rs.next()){
				UsuarioBean ub = new UsuarioBean();
				
				ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
				ub.setId(rs.getInt("USUA_CD_ID"));
				ub.setLogin(rs.getString("USUA_DS_LOGIN"));
				ub.setPapel(rs.getInt("PAPE_CD_ID"));
				ub.setSenha("");
				c.add(ub);
			}
			return c;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	
	public UsuarioBean getUsuarioPorLogin(String login){
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT * FROM USUARIO WHERE USUA_DS_LOGIN LIKE ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, login);
			rs = ps.executeQuery();

			rs.next();
			UsuarioBean ub = new UsuarioBean();
			
			ub.setDescricao(rs.getString("USUA_DS_USUARIO"));
			ub.setId(rs.getInt("USUA_CD_ID"));
			ub.setLogin(rs.getString("USUA_DS_LOGIN"));
			ub.setPapel(rs.getInt("PAPE_CD_ID"));
			ub.setSenha("");
			
			return ub;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
				
	}
	
}
